import math
import time

import xlrd

import mysql.connector
from fdfs_client.client import get_tracker_conf, Fdfs_client


def initMysql():
  global mydb
  global mycursor
  mydb = mysql.connector.connect(
    host='8.129.219.66',
    user='guifangtong',
    passwd='guifangtong1234!@#',
    database='guifangtong'
  )
  # mydb = mysql.connector.connect(
  #   host='localhost',
  #   user='root',
  #   passwd='root1234',
  #   database='guifangtong'
  # )
  mycursor = mydb.cursor()

def readExcel(f):
  bok = xlrd.open_workbook(f)
  return bok

# def findFangs(fid):
#   initMysql()
#   table_name = 't_fang'
#   sql = "select * from " + table_name + " where id = " + str(fid)
#   print('sql', sql)
  # mycursor.execute(sql)
  # myresult = mycursor.fetchall()  # fetchall() 获取所有记录
  # return myresult


# 房源剩余佣金已修改(1)
def updateFangDetail(fid, remark):
    initMysql()
    table_name = 't_fang'
    # print('房源ID', fid)
    # print('佣金说明', remark)
    cmd_sql = 'UPDATE ' + table_name + ' set commission = "' + remark +'" , commission_bak="'+remark+'", commisson_fix="'+remark+'" where id = ' + str(fid)
    # print(cmd_sql)
    try:
      mycursor.execute(cmd_sql)
      mydb.commit()
      print('更新成功了', fid)
    except Exception as e:
      mydb.rollback()  # 发生错误时回滚
      print('更新出错了',fid, e)
      mydb.close()



if __name__ == '__main__':
  path = r'D:\work\vuework\crash-handler-management\pythonManger\房源剩余佣金已修改(1).xls'
  sheets = readExcel(path)
  colx = 0
  phone_colx = 0
  all_sheets = sheets.sheet_names()
  page = 0
  order_arr = []
  for k in range(len(all_sheets)):
    sheet = sheets.sheet_by_index(k)
    # rows_data = sheet.row_values(0)
    order_nums = sheet.col_values(0)
    names = sheet.col_values(1)
    remarks = sheet.col_values(2)
    for i in range(len(order_nums)):
      if i > 0:
        fid = order_nums[i]
        remark = remarks[i]
        updateFangDetail(math.trunc(fid),remark)
        # time.sleep(2)
        # print('fid', math.trunc(fid), remark)
